import json
import os
from datetime import datetime, timedelta
from pathlib import Path
from typing import List, Optional, Literal

import pandas as pd
from fastapi import FastAPI, Depends, Query
from fastapi.middleware.cors import CORSMiddleware
from sqlalchemy.orm import Session

import crud, schemas, utils, models
from database import get_db
from es_client import ElasticSearchClient

app = FastAPI(title="站点管理系统API")
config = utils.load_config()
es_client = ElasticSearchClient()
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # Allows all origins
    allow_credentials=True,
    allow_methods=["*"],   # Allows all methods
    allow_headers=["*"],   # Allows all headers
)


@app.get("/api/report/stats", response_model=schemas.SiteStats)
def get_site_stats(db: Session = Depends(get_db)):
    """获取站点统计信息"""
    return crud.get_site_statistics(db)


@app.get("/api/report/clusters", response_model=List[schemas.ClusterStats])
def get_cluster_stats(db: Session = Depends(get_db)):
    """获取群组统计信息"""
    cluster_stats = crud.get_cluster_statistics(db)
    return [{"cluster_name": name, "site_count": count} for name, count in cluster_stats]


@app.get("/api/report/sites", response_model=schemas.PaginatedSiteList)
def get_sites(
    db: Session = Depends(get_db),
    page: int = Query(default=1, ge=1),
    pageSize: int = Query(default=10, ge=1),
    cluster_id: str = Query(default=None, description="群组id"),
    site_status: int = Query(default=None, description="站点状态数值（0-未启用，1-启用）")
):
    try :
        """获取站点列表信息（分页）"""
        skip = (page - 1) * pageSize
        result = crud.get_site_list(
            db,
            skip=skip,
            limit=pageSize,
            cluster_id=cluster_id,
            site_status=site_status,
            for_excel=False  # API调用不使用Excel格式
        )
        get_site_statistics = crud.get_site_statistics(db, cluster_id=cluster_id, site_status=site_status);
        return {"success":"true","data":result,"site_statistics":get_site_statistics}
    except Exception as e:
        return {"success":"false","data":None,"site_statistics":None}


@app.get("/api/report/excel")
def download_excel_report(db: Session = Depends(get_db)):
    """下载Excel报表"""
    try:
        # 获取数据
        site_stats = crud.get_site_statistics(db)
        cluster_stats = crud.get_cluster_statistics(db)
        site_list = crud.get_site_list(db, for_excel=True)

        # 生成文件名（添加时间戳避免重名）
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"台站信息报表_{timestamp}.xlsx"

        # 构建保存路径
        save_dir = os.path.join(config["baseUrl"], "downfile", "excel")
        # 确保目录存在
        Path(save_dir).mkdir(parents=True, exist_ok=True)

        # 完整的文件保存路径
        file_path = os.path.join(save_dir, filename)

        # 生成Excel文件并保存到指定路径
        utils.generate_excel_file(site_stats, cluster_stats, site_list, file_path)

        # 构建返回给前端的相对路径
        relative_path = f"/downfile/excel/{filename}"

        return {
            "success": "true",
            "data": relative_path,
            "message": "报表导出成功"
        }

    except Exception as e:
        print(f"Error in download_excel_report: {str(e)}")
        import traceback
        traceback.print_exc()
        return {
            "success": "false",
            "message": f"导出报表失败: {str(e)}",
            "data": None
        }

@app.get("/api/warn/alarms")
async def find_alarms_data(
        db: Session = Depends(get_db),
        cluster_id: Optional[str] = None,
        alarm_status: Optional[str] = None,
        alarm_content: Optional[str] = None,
        alarm_grade: Optional[str] = Query(None, description="报警等级，多个用逗号分隔：0-离线；1-市电；2-载波；3-音频；4-节目；5-前端；6-环境"),
        device_id: Optional[str] = None,
        site_id: Optional[str] = None,
        dispose_status: Optional[str] = None,
        duration_stage: Optional[Literal["1", "2", "3", "4", "5"]] = Query(
                None,
                description="报警持续时间阶段：1-1天以内，2-1-3天，3-3-7天，4-7-30天，5-30天以上"
            ),
        grade_type: Optional[str] = Query("device", description="告警等级类型，device或site"),
        start_time: Optional[int] = None,
        end_time: Optional[int] = None,
        page: int = Query(default=1, ge=1),
        page_size: int = Query(default=10, ge=1),
        sortby: str = "alarm_time",
        order: str = "desc"
):
    """查询告警数据"""
    try:
        must_conditions = []

        # 根据grade_type设置默认的alarm_grade
        if grade_type == "device":
            valid_grades = [0, 1, 5]
            if alarm_grade:
                alarm_grades = alarm_grade.split(",")
                # 确保alarm_grade在valid_grades范围内
                if not all(int(grade) in valid_grades for grade in alarm_grades):
                    raise ValueError("无效的alarm_grade参数，必须在[0, 1, 5]范围内")
                must_conditions.append({"terms": {"alarm_grade": [int(grade) for grade in alarm_grades]}})
            else:
                must_conditions.append({"terms": {"alarm_grade": valid_grades}})
        elif grade_type == "site":
            valid_grades = [2, 3, 4, 6]
            if alarm_grade:
                alarm_grades = alarm_grade.split(",")
                # 确保alarm_grade在valid_grades范围内
                if not all(int(grade) in valid_grades for grade in alarm_grades):
                    raise ValueError("无效的alarm_grade参数，必须在[2, 3, 4, 6]范围内")
                must_conditions.append({"terms": {"alarm_grade": [int(grade) for grade in alarm_grades]}})
            else:
                must_conditions.append({"terms": {"alarm_grade": valid_grades}})
        elif grade_type == "all":
            if alarm_grade:
                must_conditions.append({"term": {"alarm_grade": alarm_grade}})
            print("查询所有等级的告警")
        else:
            raise ValueError("无效的grade_type参数")

        # 添加查询条件
        if cluster_id is not None and site_id is None:
            # 查询群组下的所有站点ID
            site_ids = db.query(models.GatewayClusterSite.site_id).filter(
                models.GatewayClusterSite.cluster_id == cluster_id
            ).all()
            # 如果找到站点，构建terms查询
            if site_ids:
                site_id_list = [site_id[0] for site_id in site_ids]
                must_conditions.append({"terms": {"site_id.keyword": site_id_list}})

        # 处理报警持续时间查询
        if duration_stage:
            # 定义时间段（毫秒）
            one_day = 24 * 60 * 60 * 1000
            three_days = 3 * one_day
            seven_days = 7 * one_day
            thirty_days = 30 * one_day

            # 构建持续时间查询条件
            duration_query = {
                "bool": {
                    "should": []
                }
            }

            # 根据不同阶段构建查询条件
            if duration_stage == "1":  # 1天以内
                duration_query["bool"]["should"] = [

                    # 已处理的告警
                    {
                        "bool": {
                            "must": [
                                {"script": {
                                    "script": {
                                        "source": "doc['alarm_endtime'].value - doc['alarm_time'].value <= params.duration",
                                        "params": {"duration": one_day}
                                    }
                                }}
                            ]
                        }
                    }
                ]
            elif duration_stage == "2":  # 1-3天
                duration_query["bool"]["should"] = [
                    {
                        "bool": {
                            "must": [
                                {"script": {
                                    "script": {
                                        "source": "def duration = doc['alarm_endtime'].value - doc['alarm_time'].value; return duration > params.min && duration <= params.max",
                                        "params": {"min": one_day, "max": three_days}
                                    }
                                }}
                            ]
                        }
                    }
                ]
            elif duration_stage == "3":  # 3-7天
                duration_query["bool"]["should"] = [

                    {
                        "bool": {
                            "must": [
                                {"script": {
                                    "script": {
                                        "source": "def duration = doc['alarm_endtime'].value - doc['alarm_time'].value; return duration > params.min && duration <= params.max",
                                        "params": {"min": three_days, "max": seven_days}
                                    }
                                }}
                            ]
                        }
                    }
                ]
            elif duration_stage == "4":  # 7-30天
                duration_query["bool"]["should"] = [
                    {
                        "bool": {
                            "must": [
                                {"script": {
                                    "script": {
                                        "source": "def duration = doc['alarm_endtime'].value - doc['alarm_time'].value; return duration > params.min && duration <= params.max",
                                        "params": {"min": seven_days, "max": thirty_days}
                                    }
                                }}
                            ]
                        }
                    }
                ]
            elif duration_stage == "5":  # 30天以上
                duration_query["bool"]["should"] = [
                    {
                        "bool": {
                            "must": [
                                {"script": {
                                    "script": {
                                        "source": "doc['alarm_endtime'].value - doc['alarm_time'].value > params.duration",
                                        "params": {"duration": thirty_days}
                                    }
                                }}
                            ]
                        }
                    }
                ]
            must_conditions.append(duration_query)

        # 处理模糊匹配条件
        if alarm_status:
            must_conditions.append({"match_phrase": {"alarm_status": alarm_status}})
        if alarm_content:
            must_conditions.append({"match_phrase": {"alarm_content": alarm_content}})
        # 处理精确匹配条件
        term_fields = {
            "device_id.keyword": device_id,
            "dispose_status": dispose_status
        }
        # 如果提供了具体的site_id，使用它覆盖群组查询
        if site_id:
            term_fields["site_id.keyword"] = site_id

        for field, value in term_fields.items():
            if value is not None:
                must_conditions.append({"term": {field: {"value": str(value)}}})

        # 处理时间范围
        # if start_time and end_time:
        #     must_conditions.append({
        #         "range": {
        #             "alarm_time": {
        #                 "gte": start_time,
        #                 "lte": end_time
        #             }
        #         }
        #     })
        if start_time and end_time:
            must_conditions.append({
                "bool": {
                    "should": [
                        {
                            "range": {
                                "alarm_time": {
                                    "gte": start_time,
                                    "lte": end_time
                                }
                            }
                        },
                        {
                            "range": {
                                "alarm_endtime": {
                                    "gte": start_time,
                                    "lte": end_time
                                }
                            }
                        }
                    ],
                    "minimum_should_match": 1  # 至少满足一个条件
                }
            })

        # 构建完整的查询体
        query = {
            "query": {
                "bool": {
                    "must": must_conditions
                }
            }
        }

        # 处理排序
        if sortby in ["site_name", "device_name"]:
            sortby = f"{sortby}.keyword"

        # 计算分页
        limit = (page - 1) * page_size

        # 构建URL参数
        url_path = f"/gd-zhiheyunv2-alarm/_search?from={limit}&size={page_size}&sort={sortby}:{order}"
        print(query)
        # 执行查询
        result = es_client.execute_url(url_path, "GET", query)

        if result and "hits" in result:
            hits = result["hits"]
            total = hits["total"]["value"] if isinstance(hits["total"], dict) else hits["total"]

            # 处理查询结果
            items = []
            all_site_ids = [hit["_source"].get("site_id") for hit in hits["hits"]]
            cluster_site_map = crud.get_clusters_by_sites(db, all_site_ids)  # 获取群组信息
            for hit in hits["hits"]:
                item = hit["_source"]
                item["alarm_id"] = hit["_id"]
                # 根据站点ID查找群组信息
                site_id = item.get("site_id")
                if site_id in cluster_site_map:
                    item["cluster_id"] = cluster_site_map[site_id]["cluster_id"]
                    item["cluster_name"] = cluster_site_map[site_id]["cluster_name"]
                else:
                    item["cluster_id"] = None
                    item["cluster_name"] = None

                items.append(item)

            return {
                "success": "true",
                "data": {
                    "list": items,
                    "count": total
                }
            }

        return {
            "success": "true",
            "data": None,
            "message": "未找到数据"
        }

    except Exception as e:
        print(f"Error in find_alarms_data: {str(e)}")
        return {
            "success": "false",
            "message": str(e),
            "error_code": "55005"
        }


@app.get("/api/warn/statistics", response_model=schemas.AlarmStatisticsResponse)
async def get_alarm_statistics(
        alarm_grades: Optional[str] = Query(None,
                                             description="报警等级，多个用逗号分隔：0-离线；1-市电；2-载波；3-音频；4-节目；5-前端；6-环境"),
        grade_type: Optional[str] = Query("device", description="告警等级类型，device或site")
):
    """根据报警等级统计告警数据"""
    try:
        # 根据grade_type设置默认的alarm_grades
        if grade_type == "device":
            valid_grades = [0, 1, 5]
            if alarm_grades:
                alarm_grades_list = alarm_grades.split(",")
                # 确保alarm_grades在valid_grades范围内
                if not all(int(grade) in valid_grades for grade in alarm_grades_list):
                    raise ValueError("无效的alarm_grades参数，必须在[0, 1, 5]范围内")
                alarm_grades = [int(grade) for grade in alarm_grades_list]
            else:
                alarm_grades = valid_grades
        elif grade_type == "site":
            valid_grades = [2, 3, 4, 6]
            if alarm_grades:
                alarm_grades_list = alarm_grades.split(",")
                # 确保alarm_grades在valid_grades范围内
                if not all(int(grade) in valid_grades for grade in alarm_grades_list):
                    raise ValueError("无效的alarm_grades参数，必须在[2, 3, 4, 6]范围内")
                alarm_grades = [int(grade) for grade in alarm_grades_list]
            else:
                alarm_grades = valid_grades
        else:
            raise ValueError("无效的grade_type参数")

        # 构建查询条件
        must_conditions = [{"terms": {"alarm_grade": alarm_grades}}]

        # 执行查询
        query = {
            "size": 0,
            "query": {
                "bool": {
                    "must": must_conditions
                }
            },
            "aggs": {
                "alarm_grade_stats": {
                    "terms": {
                        "field": "alarm_grade",
                        "size": len(alarm_grades)  # 只返回指定的告警等级
                    }
                }
            }
        }

        result = es_client.execute_url("/gd-zhiheyunv2-alarm/_search", method="GET", body=query)

        if result and "aggregations" in result:
            stats = result["aggregations"]["alarm_grade_stats"]["buckets"]
            statistics = [
                schemas.AlarmGradeStats(
                    alarm_grade=bucket["key"],
                    alarm_grade_name=utils.get_grade_name(bucket["key"]),
                    count=bucket["doc_count"]
                )
                for bucket in stats
            ]
            # 确保返回完整的统计信息
            for grade in alarm_grades:
                if grade not in [stat.alarm_grade for stat in statistics]:
                    statistics.append(schemas.AlarmGradeStats(alarm_grade=grade, alarm_grade_name=utils.get_grade_name(grade), count=0))

            return schemas.AlarmStatisticsResponse(success="true", data=statistics)

        return schemas.AlarmStatisticsResponse(
            success="false",
            message="查询统计数据失败"
        )

    except Exception as e:
        print(f"Error in get_alarm_statistics: {str(e)}")
        return schemas.AlarmStatisticsResponse(
            success="false",
            message="统计告警数据出错"
        )

@app.get("/api/warn/export")
async def export_alarm_report(
        db: Session = Depends(get_db),
        cluster_id: Optional[str] = None,
        alarm_status: Optional[str] = None,
        alarm_content: Optional[str] = None,
        alarm_grade: Optional[int] = None,
        device_id: Optional[str] = None,
        site_id: Optional[str] = None,
        dispose_status: Optional[str] = None,
        duration_stage: Optional[str] = None,
        start_time: Optional[int] = None,
        end_time: Optional[int] = None,
        sortby: str = "alarm_time",
        order: str = "desc",
        page: int = 1,
        page_size: int = 100000,
        grade_type: Optional[str] = Query("device", description="告警等级类型，device或site")
):
    """导出告警报表"""
    try:
        one_day = 24 * 60 * 60 * 1000
        three_days = 3 * one_day
        seven_days = 7 * one_day
        thirty_days = 30 * one_day

        # 根据grade_type设置grade_names和grade_values
        if grade_type == "device":
            grade_names = ["掉线告警", "掉电告警", "设备告警"]
            grade_values = [0, 1, 5]
        elif grade_type == "site":
            grade_names = ["节目", "载波", "音频", "环境"]
            grade_values = [4, 2, 3, 6]
        else:
            raise ValueError("无效的grade_type参数")

        # 构建基础查询条件
        must_conditions = []

        # 处理duration_stage参数
        if duration_stage:
            duration_query = {
                "script": {
                    "script": {
                        "source": """
                                long duration = doc['alarm_endtime'].value - doc['alarm_time'].value;
                                if (params.stage == '1') return duration <= params.one_day;
                                if (params.stage == '2') return duration > params.one_day && duration <= params.three_days;
                                if (params.stage == '3') return duration > params.three_days && duration <= params.seven_days;
                                if (params.stage == '4') return duration > params.seven_days && duration <= params.thirty_days;
                                if (params.stage == '5') return duration > params.thirty_days;
                                return false;
                                """,
                        "params": {
                            "stage": duration_stage,
                            "one_day": one_day,
                            "three_days": three_days,
                            "seven_days": seven_days,
                            "thirty_days": thirty_days
                        }
                    }
                }
            }
            must_conditions.append(duration_query)

        # 处理群组ID查询
        if cluster_id and not site_id:
            site_ids = crud.get_cluster_sites(db, cluster_id)
            if site_ids:
                must_conditions.append({
                    "terms": {
                        "site_id.keyword": site_ids
                    }
                })

        # 处理其他查询条件
        if alarm_status:
            must_conditions.append({"term": {"alarm_status": alarm_status}})
        if alarm_content:
            must_conditions.append({"match_phrase": {"alarm_content": alarm_content}})
        if device_id:
            must_conditions.append({"term": {"device_id.keyword": device_id}})
        if site_id:
            must_conditions.append({"term": {"site_id.keyword": site_id}})
        if dispose_status:
            must_conditions.append({"term": {"dispose_status": dispose_status}})
        if start_time and end_time:
            must_conditions.append({
                "range": {
                    "alarm_time": {
                        "gte": start_time,
                        "lte": end_time
                    }
                }
            })

        # 根据alarm_grade决定查询的告警等级
        if alarm_grade is not None:
            must_conditions.append({"term": {"alarm_grade": alarm_grade}})
        else:
            must_conditions.append({"terms": {"alarm_grade": grade_values}})  # 使用grade_values作为查询条件

        # 优化1: 使用单个聚合查询获取所有统计数据
        stats_query = {
            "size": 0,
            "query": {
                "bool": {
                    "must": must_conditions
                }
            },
            "aggs": {
                # 告警等级统计
                "grade_stats": {
                    "terms": {
                        "field": "alarm_grade",
                        "size": len(grade_values)
                    }
                },
                # 持续时间统计
                "duration_stats": {
                    "range": {
                        "script": {
                            "source": "doc['alarm_endtime'].value - doc['alarm_time'].value",
                            "lang": "painless"
                        },
                        "ranges": [
                            {"to": one_day, "key": "1d"},
                            {"from": one_day, "to": three_days, "key": "3d"},
                            {"from": three_days, "to": seven_days, "key": "7d"},
                            {"from": seven_days, "to": thirty_days, "key": "30d"},
                            {"from": thirty_days, "key": "30d+"}
                        ]
                    }
                },
                # 群组维度统计
                "site_stats": {
                    "terms": {
                        "field": "site_id.keyword",
                        "size": 10000
                    },
                    "aggs": {
                        "grade_counts": {
                            "terms": {
                                "field": "alarm_grade",
                                "size": len(grade_values)
                            }
                        }
                    }
                }
            }
        }

        stats_result = es_client.execute_url(
            "/gd-zhiheyunv2-alarm/_search",
            method="GET",
            body=stats_query
        )

        # 处理统计结果
        all_data = []

        # 1. 处理告警等级统计
        grade_data = {grade: 0 for grade in grade_values}
        for bucket in stats_result["aggregations"]["grade_stats"]["buckets"]:
            grade_data[bucket["key"]] = bucket["doc_count"]

        all_data.append(grade_names)
        all_data.append([grade_data[grade] for grade in grade_values])
        all_data.extend([[None] * len(grade_names)] * 2)  # 添加空行

        # 2. 处理持续时间统计
        duration_names = ["报警持续时间-1天以内", "报警持续时间-1-3天", "报警持续时间-3-7天",
                          "报警持续时间-7-30天", "报警持续时间-30天以上"]
        duration_counts = []

        for bucket in stats_result["aggregations"]["duration_stats"]["buckets"]:
            duration_counts.append(bucket["doc_count"])

        all_data.append(duration_names)
        all_data.append(duration_counts)
        all_data.extend([[None] * len(duration_names)] * 2)  # 添加空行

        # 3. 处理站点维度统计
        cluster_columns = ["序号", "群组名称", "站点名称"] + grade_names
        all_data.append(cluster_columns)

        # 获取站点到群组的映射
        site_buckets = stats_result["aggregations"]["site_stats"]["buckets"]
        site_ids = [bucket["key"] for bucket in site_buckets]
        cluster_site_map = crud.get_clusters_by_sites(db, site_ids)


        # 构建站点统计数据
        site_stats = {}
        for bucket in site_buckets:
            site_id = bucket["key"]
            if site_id in cluster_site_map:
                cluster_info = cluster_site_map[site_id]
                cluster_id = cluster_info["cluster_id"]
                cluster_name = cluster_info["cluster_name"]

                # 初始化站点统计数据
                if site_id not in site_stats:
                    site_stats[site_id] = {
                        "cluster_id": cluster_id,
                        "cluster_name": cluster_name,
                        "site_name": cluster_info["site_name"],
                        "grades": {grade: 0 for grade in grade_values}
                    }

                # 累加每个站点的统计数据
                for grade_bucket in bucket["grade_counts"]["buckets"]:
                    grade = grade_bucket["key"]
                    if grade in grade_values:
                        site_stats[site_id]["grades"][grade] += grade_bucket["doc_count"]

        # 按群组名称排序站点统计数据
        sorted_site_stats = []
        # 首先按群组分组
        cluster_groups = {}
        for site_id, stats in site_stats.items():
            cluster_name = stats["cluster_name"]
            if cluster_name not in cluster_groups:
                cluster_groups[cluster_name] = []
            cluster_groups[cluster_name].append((site_id, stats))

        # 按群组名称排序，并将每个群组内的站点添加到最终列表
        idx = 1
        for cluster_name in sorted(cluster_groups.keys()):
            # 获取该群组下的所有站点
            sites = cluster_groups[cluster_name]
            # 可以在这里添加站点名称的排序如果需要
            # sites.sort(key=lambda x: x[1]["site_name"])

            # 添加该群组下的所有站点数据
            for site_id, stats in sites:
                row_data = [
                               idx,
                               stats["cluster_name"],
                               stats["site_name"]
                           ] + [stats["grades"][grade] for grade in grade_values]
                sorted_site_stats.append(row_data)
                idx += 1

        # 将排序后的数据添加到 all_data
        all_data.extend(sorted_site_stats)


        # 生成文件名（添加时间戳避免重名）
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        if grade_type == "device":
            filename1 = f"前端系统告警报表_{timestamp}.xlsx"
        elif grade_type == "site":
            filename1 = f"台站监测告警报表_{timestamp}.xlsx"
        else:
            filename1 = "告警报表_{timestamp}.xlsx"
        # 构建保存路径
        save_dir = os.path.join(config["baseUrl"], "downfile", "excel")
        # 确保目录存在
        Path(save_dir).mkdir(parents=True, exist_ok=True)

        # 完整的文件保存路径
        file_path = os.path.join(save_dir, filename1)

        # 创建Excel文件
        with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
            workbook = writer.book
            # 定义表头格式
            header_format = workbook.add_format({
                'bold': True,  # 加粗
                'font_color': 'black',  # 黑色字体
                'align': 'center',  # 居中对齐
                'valign': 'vcenter',  # 垂直居中
                'border': 1  # 边框
            })
            # 定义数据格式
            data_format = workbook.add_format({
                'border': 1,  # 边框
                'align': 'center',  # 居中对齐
                'valign': 'vcenter'  # 垂直居中
            })
            # 保存统计信息
            df = pd.DataFrame(all_data)
            df.to_excel(writer, sheet_name='统计信息', index=False, header=False)
            # 调整列宽
            worksheet = writer.sheets['统计信息']

            # 为所有数据添加格式
            empty_row_count = 0  # 用于跟踪连续空行
            # 为所有数据添加格式
            for row_num, row_data in enumerate(all_data):
                # 检查是否是空行
                is_empty_row = all(cell == "" or cell is None for cell in row_data)

                if is_empty_row:
                    empty_row_count += 1
                    continue  # 跳过空行的格式设置
                else:
                    empty_row_count = 0

                # 检查是否是表头行
                is_header = (
                        row_num == 0 or  # 第一部分的表头
                        (row_num > 0 and empty_row_count > 0)  # 其他部分的表头
                )

                for col_num, value in enumerate(row_data):
                    if is_header:
                        worksheet.write(row_num, col_num, value, header_format)
                    else:
                        worksheet.write(row_num, col_num, value, data_format)
            worksheet.set_column('A:Z', 30)

            # 第二个sheet：告警列表
            list_query = {
                "query": {"bool": {"must": must_conditions}},
                "sort": [{sortby: {"order": order}}],
                "size": page_size
            }
            print("list_query:"+json.dumps(list_query))
            list_result = es_client.execute_url(
                "/gd-zhiheyunv2-alarm/_search",
                method="GET",
                body=list_query
            )
            if list_result and "hits" in list_result:
                alarm_list = []
                for idx, hit in enumerate(list_result["hits"]["hits"], 1):
                    item = hit["_source"]
                    cluster_name = crud.get_cluster_name_by_site_id(db, item.get("site_id", ""))

                    alarm_list.append({
                        "序号": idx,
                        "群组名称": cluster_name,
                        "站点名称": item.get("site_name", ""),
                        "设备名称": item.get("device_name", ""),
                        "报警时间": utils.format_timestamp(item.get("alarm_time")),
                        "报警内容": item.get("alarm_content", ""),
                        "报警等级": utils.get_grade_name(item.get("alarm_grade")),
                        "消警方式": utils.get_eliminate_type_name(item.get("eliminate_type")),
                        "消警时间": utils.format_timestamp(item.get("eliminate_time")),
                        "持续报警时间": utils.format_duration(
                            item.get("alarm_endtime", 0) - item.get("alarm_time", 0)
                        ),
                        "报警次数": item.get("alarm_num", 0),
                        "是否消警": utils.get_alarm_status_name(item.get("alarm_status")),
                        "是否处理": utils.get_dispose_status_name(item.get("dispose_status")),
                        "处理时间": utils.format_timestamp(item.get("dispose_time")),
                        "处理人员": item.get("dispose_username", "")
                    })

                # 创建DataFrame并写入Excel
                df_alarms = pd.DataFrame(alarm_list)
                df_alarms.to_excel(writer, sheet_name='告警列表', index=False)

                # 获取worksheet并应用格式
                worksheet_alarms = writer.sheets['告警列表']

                # 应用表头格式
                for col_num, value in enumerate(df_alarms.columns):
                    worksheet_alarms.write(0, col_num, value, header_format)

                # 应用数据格式
                for row_num in range(1, len(df_alarms) + 1):
                    for col_num in range(len(df_alarms.columns)):
                        value = df_alarms.iloc[row_num - 1, col_num]
                        worksheet_alarms.write(row_num, col_num, value, data_format)

                # 调整列宽
                worksheet_alarms.set_column('A:Z', 15)

        # 返回Excel文件
        # 构建返回给前端的相对路径
        relative_path = f"/downfile/excel/{filename1}"
        return {
            "success": "true",
            "data": relative_path,
            "message": "报表导出成功"
        }
    except Exception as e:
        print(f"Error in export_alarm_report: {str(e)}")
        import traceback
        traceback.print_exc()
        return {"success": "false", "message": f"导出报表失败: {str(e)}"}


def get_total_hits(result: dict) -> int:
    """安全地获取ES查询结果的总数"""
    if not result or "hits" not in result:
        return 0
    total = result["hits"]["total"]
    if isinstance(total, dict):
        return total.get("value", 0)
    return total if isinstance(total, int) else 0


@app.get("/api/warn/export/simple")
async def export_alarm_report_simple(
        db: Session = Depends(get_db),
        cluster_id: Optional[str] = None,
        alarm_status: Optional[str] = None,
        alarm_content: Optional[str] = None,
        alarm_grade: Optional[int] = None,
        device_id: Optional[str] = None,
        site_id: Optional[str] = None,
        dispose_status: Optional[str] = None,
        duration_stage: Optional[str] = None,
        start_time: Optional[int] = None,
        end_time: Optional[int] = None,
        sortby: str = "alarm_time",
        order: str = "desc",
        page: int = 1,
        page_size: int = 1000000
):
    """导出简化版告警报表"""
    try:
        one_day = 24 * 60 * 60 * 1000
        three_days = 3 * one_day
        seven_days = 7 * one_day
        thirty_days = 30 * one_day
        # 构建ES查询条件
        must_conditions = []

        if cluster_id:
            cluster_site_ids = crud.get_cluster_sites(db, cluster_id)
            if cluster_site_ids:
                must_conditions.append({"terms": {"site_id": cluster_site_ids}})
        if site_id:
            must_conditions.append({"term": {"site_id.keyword": site_id}})
        if alarm_status is not None:
            must_conditions.append({"term": {"alarm_status": alarm_status}})
        if alarm_content:
            must_conditions.append({"match": {"alarm_content": alarm_content}})
        if alarm_grade is not None:
            must_conditions.append({"term": {"alarm_grade": alarm_grade}})
        if device_id:
            must_conditions.append({"term": {"device_id.keyword": device_id}})
        if dispose_status is not None:
            must_conditions.append({"term": {"dispose_status": dispose_status}})

        # 处理duration_stage参数
        if duration_stage:
            duration_query = {
                "script": {
                    "script": {
                        "source": """
                                    long duration = doc['alarm_endtime'].value - doc['alarm_time'].value;
                                    if (params.stage == '1') return duration <= params.one_day;
                                    if (params.stage == '2') return duration > params.one_day && duration <= params.three_days;
                                    if (params.stage == '3') return duration > params.three_days && duration <= params.seven_days;
                                    if (params.stage == '4') return duration > params.seven_days && duration <= params.thirty_days;
                                    if (params.stage == '5') return duration > params.thirty_days;
                                    return false;
                                    """,
                        "params": {
                            "stage": duration_stage,
                            "one_day": one_day,
                            "three_days": three_days,
                            "seven_days": seven_days,
                            "thirty_days": thirty_days
                        }
                    }
                }
            }
            must_conditions.append(duration_query)
        if start_time and end_time:
            must_conditions.append({
                "range": {
                    "alarm_time": {
                        "gte": start_time,
                        "lte": end_time
                    }
                }
            })

        # 构建ES查询体
        query_body = {
            "query": {"bool": {"must": must_conditions}} if must_conditions else {"match_all": {}},
            "sort": [{sortby: {"order": order}}],
            "from": (page - 1) * page_size,
            "size": page_size
        }
        print("query_body:"+ json.dumps(query_body))
        # 执行ES查询
        list_result = es_client.execute_url(
            "/gd-zhiheyunv2-alarm/_search",
            method="GET",
            body=query_body
        )

        total_count = list_result['hits']['total']
        print(f"总条数: {total_count}")
        current_count = len(list_result['hits']['hits'])
        print(f"当前返回条数: {current_count}")
        # 生成Excel文件
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"经信局数据_{timestamp}.xlsx"
        save_dir = os.path.join(config["baseUrl"], "downfile", "excel")
        Path(save_dir).mkdir(parents=True, exist_ok=True)
        file_path = os.path.join(save_dir, filename)

        # 预处理数据 - 批量获取群组名称
        if list_result and "hits" in list_result:
            hits = list_result["hits"]["hits"]
            site_ids = {hit["_source"].get("site_id", "") for hit in hits}
            cluster_names = {}

            # 批量查询群组名称
            site_cluster_map = crud.get_clusters_by_sites(db, list(site_ids))
            for site_id, cluster_info in site_cluster_map.items():
                cluster_names[site_id] = cluster_info.get("cluster_name", "")

            # 构建数据列表
            data = []
            for idx, hit in enumerate(hits, 1):
                item = hit["_source"]
                site_id = item.get("site_id", "")

                data.append({
                    "序号": idx,
                    "群组": cluster_names.get(site_id, ""),
                    "站点": item.get("site_name", ""),
                    "报警故障": item.get("alarm_content", ""),
                    "发生时间": utils.format_timestamp(item.get("alarm_time")),
                    "结束时间": utils.format_timestamp(item.get("alarm_endtime"))
                })

            # 创建DataFrame
            df = pd.DataFrame(data)

            # 使用 DataFrame 一次性写入Excel
            with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
                # 获取workbook和worksheet对象
                workbook = writer.book

                # 定义格式
                header_format = workbook.add_format({
                    'bold': True,
                    'font_color': 'black',
                    'align': 'center',
                    'valign': 'vcenter',
                    'border': 1
                })

                data_format = workbook.add_format({
                    'border': 1,
                    'align': 'center',
                    'valign': 'vcenter'
                })

                # 分批写入数据，每个sheet最多10万条数据
                max_rows_per_sheet = 100000
                total_rows = len(data)
                sheet_count = (total_rows // max_rows_per_sheet) + 1

                for sheet_index in range(sheet_count):
                    start_row = sheet_index * max_rows_per_sheet
                    end_row = min(start_row + max_rows_per_sheet, total_rows)

                    # 创建新的sheet
                    sheet_name = f'告警内容_{sheet_index + 1}'
                    worksheet = workbook.add_worksheet(sheet_name)

                    # 写入表头
                    headers = ["序号", "群组", "站点", "报警故障", "发生时间", "结束时间"]
                    for col, header in enumerate(headers):
                        worksheet.write(0, col, header, header_format)

                    # 写入数据
                    for row in range(start_row, end_row):
                        row_data = data[row]
                        for col, value in enumerate(row_data.values()):
                            worksheet.write(row + 1 - start_row, col, value, data_format)

                    # 调整列宽
                    worksheet.set_column('A:A', 8)  # 序号列
                    worksheet.set_column('B:C', 15)  # 群组和站点列
                    worksheet.set_column('D:D', 40)  # 报警故障列
                    worksheet.set_column('E:F', 20)  # 时间列

                # 应用表头格式
                # for col_num, value in enumerate(df.columns.values):
                #     worksheet.write(0, col_num, value, header_format)

                # 批量应用数据格式
                # worksheet.set_column('A:A', 8, data_format)  # 序号列
                # worksheet.set_column('B:C', 15, data_format)  # 群组和站点列
                # worksheet.set_column('D:D', 40, data_format)  # 报警故障列
                # worksheet.set_column('E:F', 20, data_format)  # 时间列

        # 返回文件路径
        relative_path = f"/downfile/excel/{filename}"
        return {
            "success": "true",
            "data": relative_path,
            "message": "报表导出成功"
        }

    except Exception as e:
        print(f"Error in export_alarm_report_simple: {str(e)}")
        import traceback
        traceback.print_exc()
        return {
            "success": "false",
            "message": f"导出报表失败: {str(e)}",
            "data": None
        }


@app.get("/api/report/word")
async def get_word_report_data(
        db: Session = Depends(get_db),
        startDate: str = Query(..., description="开始日期，格式：yyyy-MM-dd"),
        endDate: str = Query(..., description="结束日期，格式：yyyy-MM-dd")
):
    """获取Word报表数据，报警等级，多个用逗号分隔：0-离线；1-市电；2-载波；3-音频；4-节目；5-前端；6-环境"""
    try:
        # 处理时间范围
        # 解析开始和结束日期
        start_date = datetime.strptime(startDate, "%Y-%m-%d")
        end_date = datetime.strptime(endDate, "%Y-%m-%d")

        # 确保结束日期在开始日期之后
        if end_date < start_date:
            return {"success": False, "message": "结束日期必须大于开始日期"}

        # 计算报告日期为当前日期
        report_date = datetime.now().strftime("%Y年%m月%d日")

        # 转换为毫秒时间戳
        start_timestamp = int(start_date.timestamp() * 1000)
        end_timestamp = int(end_date.replace(hour=23, minute=59, second=59).timestamp() * 1000)

        # 1. 系统故障统计
        error_grade_stats = await get_error_grade_stats(start_timestamp, end_timestamp)

        # 2. 安全播出情况分析
        safe_broadcast_stats = calculate_safe_broadcast_stats(error_grade_stats)

        # 3. 监测系统前端自检情况
        front_end_stats = calculate_front_end_stats(error_grade_stats)

        # 4. 故障持续1天以上的系统故障情况
        error_continue_stats = await get_error_continue_stats(start_timestamp, end_timestamp)

        # 5. 故障持续1天以上的站点列表
        error_continue_list = await get_error_continue_list(start_timestamp, end_timestamp)

        # 格式化日期
        start_time = start_date.strftime("%Y年%m月%d日")
        end_time = end_date.strftime("%Y年%m月%d日")

        return {
            "success": True,
            "data": {
                "startTime": start_time,
                "endTime": end_time,
                "reportTime": report_date,
                "errorGrade": error_grade_stats,
                "safeBroadcast": safe_broadcast_stats,
                "frontEndCheck": front_end_stats,
                "errorContinue": error_continue_stats,
                "errorContinueList": error_continue_list
            }
        }

    except Exception as e:
        print(f"Error in get_word_report_data: {str(e)}")
        import traceback
        traceback.print_exc()
        return {"success": False, "message": str(e)}


async def get_error_grade_stats(start_time: int, end_time: int):
    """获取系统故障统计"""
    url_path = "/gd-zhiheyunv2-alarm/_search"

    # 1. 获取各报警等级的站点数量
    grade_query = {
        "query": {
            "bool": {
                "must": [
                    {
                        "bool": {
                            "should": [
                                {"range": {"alarm_time": {"gte": start_time, "lte": end_time}}},
                                {"range": {"alarm_endtime": {"gte": start_time, "lte": end_time}}}
                            ]
                        }
                    },
                    {"term": {"alarm_status": 1}}  # 添加 alarm_status=1 条件，未消警=1
                ]
            }
        },
        "aggs": {
            "grade_stats": {
                "terms": {
                    "field": "alarm_grade",
                    "size": 10
                },
                "aggs": {
                    "unique_sites": {
                        "cardinality": {
                            "field": "site_id.keyword"
                        }
                    }
                }
            }
        }
    }

    result = es_client.execute_url(url_path=url_path, body=grade_query)

    # print(json.dumps(result))
    # 初始化所有等级的计数为0
    stats = {f"grade{i}": 0 for i in range(7)}

    # 更新统计数据
    for bucket in result["aggregations"]["grade_stats"]["buckets"]:
        grade = bucket["key"]
        unique_site_count = bucket["unique_sites"]["value"]
        stats[f"grade{grade}"] = int(unique_site_count)  # 转换为整数

    # 2. 获取D2（市电、载波、音频共同影响的站点数量）
    d2_query = {
        "query": {
            "bool": {
                "must": [
                    {
                        "bool": {
                            "should": [
                                {"range": {"alarm_time": {"gte": start_time, "lte": end_time}}},
                                {"range": {"alarm_endtime": {"gte": start_time, "lte": end_time}}}
                                ]
                        }
                    },
                    {"term": {"alarm_status": 1}},  # 添加 alarm_status=1 条件，未消警=1
                    {"terms": {"alarm_grade": [1, 2, 3]}}  # 市电、载波、音频
                ]
            }
        },
        "aggs": {
            "unique_sites": {
                "cardinality": {
                    "field": "site_id.keyword"
                }
            }
        }
    }

    d2_result = es_client.execute_url(url_path=url_path, body=d2_query)
    stats["D2"] = int(d2_result["aggregations"]["unique_sites"]["value"])

    # 3. 获取D3（离线、前端、环境共同影响的站点数量）
    d3_query = {
        "query": {
            "bool": {
                "must": [
                    {
                        "bool": {
                            "should": [
                                {"range": {"alarm_time": {"gte": start_time, "lte": end_time}}},
                                {"range": {"alarm_endtime": {"gte": start_time, "lte": end_time}}}
                                ]
                        }
                    },
                    {"term": {"alarm_status": 1}},  # 添加 alarm_status=1 条件，未消警=1
                    {"terms": {"alarm_grade": [0, 5, 6]}}  # 离线、前端、环境
                ]
            }
        },
        "aggs": {
            "unique_sites": {
                "cardinality": {
                    "field": "site_id.keyword"
                }
            }
        }
    }

    d3_result = es_client.execute_url(url_path=url_path, body=d3_query)
    stats["D3"] = int(d3_result["aggregations"]["unique_sites"]["value"])

    return stats


def calculate_safe_broadcast_stats(error_grade_stats):
    """计算安全播出情况分析"""
    # 计算D2：市电、载波、音频的站点数量总和
    D2 = error_grade_stats["grade1"] + error_grade_stats["grade2"] + error_grade_stats["grade3"]

    # 计算抽检比例
    # check_ratio = 436 / 100
    check_ratio= 1
    # 计算系统播出故障率
    error_percent = (D2 / 100) * check_ratio
    #设置最大为1
    if error_percent > 1:
        error_percent = 1
    normal_percent = 1 - error_percent

    return {
        "errorPercent": round(error_percent, 3),
        "normalPercent": round(normal_percent, 3)
    }


def calculate_front_end_stats(error_grade_stats):
    """计算监测系统前端自检情况"""
    # 计算D3：离线、前端、环境的站点数量总和
    D3 = error_grade_stats["grade0"] + error_grade_stats["grade5"] + error_grade_stats["grade6"]

    # 计算抽检比例
    # check_ratio = 436 / 100
    check_ratio = 1

    # 计算监测前端站点故障率
    error_percent = (D3 / 100) * check_ratio
    # 设置最大为1
    if error_percent > 1:
        error_percent = 1
    normal_percent = 1 - error_percent

    # 计算监测前端在线率
    offline_percent = (error_grade_stats["grade0"] / 100) * check_ratio
    # 设置最大为1
    if offline_percent > 1:
        offline_percent = 1
    online_percent = 1 - offline_percent

    return {
        "errorPercent": round(error_percent, 3),
        "normalPercent": round(normal_percent, 3),
        "onlinePercent": round(online_percent, 3)
    }


async def get_error_continue_stats(start_time: int, end_time: int):
    """获取故障持续1天以上的系统故障情况"""
    one_day = 24 * 60 * 60 * 1000  # 24小时的毫秒数

    query = {
        "query": {
            "bool": {
                "must": [
                    {
                        "bool": {
                            "should": [
                                {"range": {"alarm_time": {"gte": start_time, "lte": end_time}}},
                                {"range": {"alarm_endtime": {"gte": start_time, "lte": end_time}}}
                            ]
                        }
                    },
                    {"term": {"alarm_status": 1}}, # 未消警=1
                    {
                        "script": {
                            "script": {
                                "source": "doc['alarm_endtime'].value - doc['alarm_time'].value > params.one_day",
                                "params": {"one_day": one_day}
                            }
                        }
                    }
                ]
            }
        },
        "aggs": {
            "grade_stats": {
                "terms": {
                    "field": "alarm_grade",
                    "size": 10
                },
                "aggs": {
                    "unique_sites": {
                        "cardinality": {
                            "field": "site_id.keyword"
                        }
                    }
                }
            }
        }
    }

    result = es_client.execute_url(url_path="/gd-zhiheyunv2-alarm/_search", body=query)

    stats = {f"grade{i}": 0 for i in range(7)}
    for bucket in result["aggregations"]["grade_stats"]["buckets"]:
        grade = bucket["key"]
        stats[f"grade{grade}"] = bucket["doc_count"]

    return stats


async def get_error_continue_list(start_time: int, end_time: int):
    """获取故障持续1天以上的站点列表"""
    one_day = 24 * 60 * 60 * 1000  # 24小时的毫秒数

    query = {
        "query": {
            "bool": {
                "must": [
                    {
                        "bool": {
                            "should": [
                                {"range": {"alarm_time": {"gte": start_time, "lte": end_time}}},
                                {"range": {"alarm_endtime": {"gte": start_time, "lte": end_time}}}
                                ]
                        }
                    },
                    {"term": {"alarm_status": 1}},  # 未消警=1
                    {
                        "script": {
                            "script": {
                                "source": "doc['alarm_endtime'].value - doc['alarm_time'].value > params.one_day",
                                "params": {"one_day": one_day}
                            }
                        }
                    }
                ]
            }
        },
        "sort": [{"alarm_time": "desc"}],
        "size": 10000  # 设置一个较大的值以获取所有数据
    }

    result = es_client.execute_url(url_path="/gd-zhiheyunv2-alarm/_search", body=query)

    error_list = []
    for hit in result["hits"]["hits"]:
        source = hit["_source"]
        duration = (source["alarm_endtime"] - source["alarm_time"]) / (60 * 60 * 1000)  # 转换为天数
        error_list.append({
            "stationName": source.get("site_name", ""),
            "errorType": utils.get_grade_name(source.get("alarm_grade")),
            "duration": round(duration, 3),
            "alarm_content": source.get("alarm_content", "")
        })


    return error_list





if __name__ == "__main__":
    import uvicorn

    uvicorn.run(app, host="0.0.0.0", port=8210)